<?php

namespace app\admin\controller\budget;

use app\common\controller\Backend;
use app\admin\model\AdminDepart;
use think\Db;
use Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Wxlsx;

/**
 * 预算汇总管理
 *
 * @icon fa fa-user
 */
class Summary extends Backend
{

    protected $searchFields = 'id,budgetname';

    /**
     * @var \app\admin\model\Report
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('BudgetSummary');
		$this->detailModel = model('BudgetDetails');
    }

	/**
     * 汇总
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
			
			$filter = json_decode($this->request->get("filter"),true);
			if(isset($filter['budgettime'])){
				$filter['budgettime'] = strtotime($filter['budgettime'].'-1-1');
				$this->request->get(["filter"=>json_encode($filter)]);
			}
			
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->paginate($limit);
			
            $result = array("total" => $list->total(), "rows" => $list->items());
			
            return json($result);
        }
        return $this->view->fetch();
    }
	
	/**
     * 生成
     */
    public function add()
    {
		if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
		
		$this->token();
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        
        $map['budgettime'] = strtotime($params['budgettime'].'-1-1');
		$map['budgettype'] = intval($params['budgettype']);
		$hasExist = $this->model->where($map)->find();
		if($hasExist)
		{
			$this->error(__('您已经生成过'.$params['budgettime'].'年度的'.($params['budgettype']==1?'基本预算':'项目预算').'了，不能重复生成'));
		}

        $result = false;
        Db::startTrans();
        try {
            $result = $this->model->allowField(true)->save($params);
			
			$departs = AdminDepart::where('pid', '<>', 0)->column('id');
			$insertDetails = [];
			foreach($departs as $vo)
			{
				$data = array(
					'summary_id' => $this->model->id,
					'depart_id' => $vo,
					'budgettype' => $params['budgettype']
				);
				$insertDetails[] = $data;
			}
			$this->detailModel->saveAll($insertDetails);
			
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success('生成成功', 'index');
    }
	
	/**
     * 删除
     */
    public function del($ids = "")
    {
        if (!$this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ? $ids : $this->request->post("ids");
        if (!$ids) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $count = $this->model->where('id', 'in', $ids)->delete();
		if ($count) {
			$detailids = $this->detailModel->where('summary_id', 'in', $ids)->column('id');
			$this->detailModel->where('summary_id', 'in', $ids)->delete();
			
			model('Report')->where('detail_id', 'in', $detailids)->delete();
			$this->success();
		}

        $this->error(__('No rows were deleted'));
    }
	
	/**
     * 导出
     */
	public function export($ids)
	{
		$row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
		
		if($row->budgettype == 1)
		{
			$template = 'report_basic_template.xlsx';
		}
		else
		{
			$template = 'report_project_template.xlsx';
		}
		$filePath = ROOT_PATH . DS . 'public' . DS . $template;
        if (!is_file($filePath)) {
            $this->error(__('No file were found'));
        }
		
		$reader = new Xlsx();
		try{
            if (!$spreadsheet = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
			
			$sheet = $spreadsheet->getActiveSheet();
			$sheet->getStyle('H')->getFont()->setBold(true);
			
			$allbudgetmoney = 0;
			$allpubfiscalbudget = 0;
			$alleducationbudget = 0;
			$allfund = 0;
			$allotherfund = 0;
			
			$details = $this->detailModel->where('summary_id', 'in', $ids)->select();
			$rows = 6;
			foreach($details as $vo)
			{
				$reports = model('Report')->where('detail_id', $vo['id'])->select();
				if($reports)
				{
					$sheet->getRowDimension($rows)->setRowHeight(30);
					$sheet->getStyle('A'.$rows.':L'.$rows)->applyFromArray(array('borders'=>array('allBorders'=>array('borderStyle' =>'thin','color' =>['argb' => '00000000']))));
					$sheet->setCellValueByColumnAndRow(1, $rows, $vo->depart->name);
					$sheet->getCellByColumnAndRow(1,$rows)->getStyle()->getFont()->setBold(true);
					$sheet->getCellByColumnAndRow(1,$rows)->getStyle()->applyFromArray(array('alignment' => array('horizontal' => 'center', 'vertical' => 'center')));
					$sheet->setCellValueByColumnAndRow(8, $rows, $vo['budgetmoney']);
					$rows++;
					$allbudgetmoney += $vo['budgetmoney'];
					
					$departpubfiscalbudget = 0;
					$departeducationbudget = 0;
					$departfund = 0;
					$departotherfund = 0;
					foreach($reports as $vo2)
					{
						$sheet->getRowDimension($rows)->setRowHeight(30);
						$sheet->getStyle('A'.$rows.':L'.$rows)->applyFromArray(array('borders'=>array('allBorders'=>array('borderStyle' =>'thin','color' =>['argb' => '00000000']))));
						$sheet->setCellValueByColumnAndRow(1, $rows, $vo2['projectname']);
						$sheet->setCellValueByColumnAndRow(2, $rows, $vo2['expendtype']);
						$sheet->setCellValueByColumnAndRow(3, $rows, $vo2['expendname']);
						$sheet->setCellValueByColumnAndRow(4, $rows, $vo2['governtype']);
						$sheet->setCellValueByColumnAndRow(5, $rows, $vo2['governname']);
						$sheet->setCellValueByColumnAndRow(6, $rows, $vo2['departtype']);
						$sheet->setCellValueByColumnAndRow(7, $rows, $vo2['departname']);
						$sheet->setCellValueByColumnAndRow(8, $rows, $vo2['budget']);
						$sheet->setCellValueByColumnAndRow(9, $rows, $vo2['pubfiscalbudget']);
						$sheet->setCellValueByColumnAndRow(10, $rows, $vo2['educationbudget']);
						$sheet->setCellValueByColumnAndRow(11, $rows, $vo2['fund']);
						$sheet->setCellValueByColumnAndRow(12, $rows, $vo2['otherfund']);
						
						$departpubfiscalbudget += $vo2['pubfiscalbudget'];
						$departeducationbudget += $vo2['educationbudget'];
						$departfund += $vo2['fund'];
						$departotherfund += $vo2['otherfund'];
						$rows++;
					}
					$sheet->setCellValueByColumnAndRow(9, $rows-count($reports)-1, $departpubfiscalbudget);
					$sheet->setCellValueByColumnAndRow(10, $rows-count($reports)-1, $departeducationbudget);
					$sheet->setCellValueByColumnAndRow(11, $rows-count($reports)-1, $departfund);
					$sheet->setCellValueByColumnAndRow(12, $rows-count($reports)-1, $departotherfund);
					
					$allpubfiscalbudget += $departpubfiscalbudget;
					$alleducationbudget += $departeducationbudget;
					$allfund += $departfund;
					$allotherfund += $departotherfund;
				}
			}
			$sheet->setCellValueByColumnAndRow(8, 5, $allbudgetmoney);
			$sheet->setCellValueByColumnAndRow(9, 5, $allpubfiscalbudget);
			$sheet->setCellValueByColumnAndRow(10, 5, $alleducationbudget);
			$sheet->setCellValueByColumnAndRow(11, 5, $allfund);
			$sheet->setCellValueByColumnAndRow(12, 5, $allotherfund);
			
			if($row->budgettype == 1)
			{
				$filename = '基本支出预算明细表';
			}
			else
			{
				$filename = '项目支出预算明细表';
			}
			// MIME 协议，文件的类型，不设置，会默认html
			header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			// MIME 协议的扩展
			header('Content-Disposition:attachment;filename='.$filename.'.xlsx');
			// 缓存控制
			header('Cache-Control:max-age=0');
			
			$writer = new Wxlsx($spreadsheet);
			$writer->save('php://output');
		} catch (Exception $exception) {
            $this->error($exception->getMessage());
        }	
	}
}
